package com.gillion.service;
import com.gillion.dao.UntilDao;
import com.gillion.entity.User;
import jxl.Sheet;
import jxl.Workbook;
import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by admin on 2016/10/31.
 */
public  class CreateSimpleExcelToDisk {
    public static List<User> getStudent() throws Exception {
        List<User> list=new ArrayList<User>();
        try {
            UntilDao dao = new UntilDao();
            dao.getConnection();
            String sql ="select * from user";
            ResultSet rs=dao.Search(sql,null);
            while (rs.next()){
                int id =rs.getInt("id");
                String name=rs.getString("name");
                String sex=rs.getString("sex");
                int num=rs.getInt("num");
                list.add(new User(id,name,sex,num));
            }
        }catch (SQLException ex){
        ex.printStackTrace();
        }
        return list;
    }
    public static List<User> getAllByExcel(String file) {
        UntilDao dao = new UntilDao();
        dao.getConnection();
        List<User> list = new ArrayList<User>();
        try {
            Workbook rwb=Workbook.getWorkbook(new File(file));
            Sheet rs=rwb.getSheet(0);//或者rwb.getSheet(0)
            int clos=rs.getColumns();//得到所有的列
            int rows=rs.getRows();//得到所有的行

            System.out.println(clos+" rows:"+rows);
            for (int i = 1; i < rows; i++) {
                for (int j = 0; j < clos; j++) {
                    //第一个是列数，第二个是行数
                    String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
                    String name=rs.getCell(j++, i).getContents();
                    String sex=rs.getCell(j++, i).getContents();
                    String num=rs.getCell(j++, i).getContents();

                    System.out.println("id:"+id+" name:"+name+" sex:"+sex+" num:"+num);
                    list.add(new User(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;

    }
    public static boolean isExist(int id){
        try {
            UntilDao dao = new UntilDao();
            dao.getConnection();
            ResultSet rs = dao.Search("select * from user where id=?", new String[]{id+""});
            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }
}